package com.classes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.TreeMap;

import org.json.simple.JSONObject;
import org.json.simple.JSONValue;

import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;

public class OutletFactory {
	private String message;
	private long outletID = 0;

	public String getMessage() {
		return "[OutletFactory] " + this.message;
	}

	public long getOutletID() {
		return this.outletID;
	}

	public String generateVendorFormSerialNo() {
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			Long serial_no = (long) 0;

			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery("SELECT SEQ_VENDOR_SERIALNO.NEXTVAL FROM DUAL");

			if (rs.next()) {
				serial_no = new Long(rs.getLong(1));
			}

			String serial_str = Long.toString(serial_no, 36);
			serial_str = String.format("%8s", serial_str);
			serial_str = serial_str.replace(" ", "0");

			serial_no = (long) 0;
			rs = statement.executeQuery("SELECT SEQ_VENDOR_SERIALNO_PREFIX.NEXTVAL FROM DUAL");

			if (rs.next()) {
				serial_no = new Long(rs.getLong(1));
			}

			String serial_str2 = Long.toString(serial_no, 36);

			return serial_str2 + serial_str;

		} catch (Exception e){
			this.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public boolean newCaseOutlet(TreeMap<String, String> post, String user){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "insert into outlet (o_merchant_id, o_name, o_reg_name, o_reg_no, "
					+ "o_license_no, o_license_type, o_license_issuer, o_license_state, "
					+ "o_license_expiry, o_risk, o_ct_name, o_ct_designation, o_ct_email, "
					+ "o_ct_tel, o_ct_fax, o_created_date, o_created_by, o_modified_date, "
					+ "o_modified_by) "
					+ "values (?, ?, ?, ?, "
					+ "?, ?, ?, ?, "
					+ "?, ?, ?, ?, ?, "
					+ "?, ?, sysdate, ?, sysdate, "
					+ "?)";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setInt(1, Integer.parseInt(post.get("merchant_id")));
				statement.setString(2, post.get("trading_name"));
				statement.setString(3, post.get("registered_name"));
				statement.setString(4, post.get("registered_no"));
				statement.setString(5, post.get("license_no"));
				statement.setString(6, post.get("license_type"));
				statement.setString(7, post.get("issuer"));
				statement.setString(8, post.get("state"));
				statement.setString(9, post.get("expiry_date"));
				statement.setString(10, post.get("risk_level"));
				statement.setString(11, post.get("contact_person"));
				statement.setString(12, post.get("designation"));
				statement.setString(13, post.get("email"));
				statement.setString(14, post.get("telephone_no"));
				statement.setString(15, post.get("fax_no"));
				statement.setString(16, user);
				statement.setString(17, user);

				statement.execute();

			} finally {
				statement.close();
			}

			Long outlet_id = null;
			Statement statement2 = conn.createStatement();

			try {
				ResultSet generatedKeys = statement2.executeQuery("SELECT SEQ_OUTLET.CURRVAL FROM DUAL");
				try {
					if (generatedKeys.next()) {
						outlet_id = new Long(generatedKeys.getLong(1));
					}
				} finally {
					generatedKeys.close();
				}
			} finally {
				statement2.close();
			}

			this.outletID = outlet_id;
			System.out.println("Inserted Outlet ID: " + this.outletID);


			JSONObject outlet_dtl_obj = new JSONObject();
			outlet_dtl_obj.put("merchant_id", post.get("merchant_id"));
			outlet_dtl_obj.put("trading_name", post.get("trading_name"));
			outlet_dtl_obj.put("registered_name", post.get("registered_name"));
			outlet_dtl_obj.put("registered_no", post.get("registered_no"));
			outlet_dtl_obj.put("license_no", post.get("license_no"));
			outlet_dtl_obj.put("license_type", post.get("license_type"));
			outlet_dtl_obj.put("issuer", post.get("issuer"));
			outlet_dtl_obj.put("state", post.get("state"));
			outlet_dtl_obj.put("expiry_date", post.get("expiry_date"));
			outlet_dtl_obj.put("risk_level", post.get("risk_level"));
			outlet_dtl_obj.put("contact_person", post.get("contact_person"));
			outlet_dtl_obj.put("designation", post.get("designation"));
			outlet_dtl_obj.put("email", post.get("email"));
			outlet_dtl_obj.put("telephone_no", post.get("telephone_no"));
			outlet_dtl_obj.put("fax_no", post.get("fax_no"));
			outlet_dtl_obj.put("address", "");
			outlet_dtl_obj.put("address_2", "");
			outlet_dtl_obj.put("address_3", "");
			outlet_dtl_obj.put("mdr_rate", "");
			outlet_dtl_obj.put("esapl", "");
			outlet_dtl_obj.put("ezypay", "");
			outlet_dtl_obj.put("stmt_option", "");
			outlet_dtl_obj.put("pymt_option", "");
			outlet_dtl_obj.put("other_info", "");
			String outlet_dtl = outlet_dtl_obj.toString();

			sql = "insert into caseoutlet (co_case_id, co_outlet_id, co_outlet_details, co_esa_ref_no, co_num_terminal, co_trm_rental, co_trm_owner, co_trm_deposit_wired, co_trm_deposit_wireless, co_trm_rental_deposit_wired, co_trm_rental_deposit_wireless, co_trm_rental_advance, co_cheque_amt, co_cheque_issuer, co_cheque_no, co_cheque_date, co_terminal_id, co_terminal_id_no, co_terminal_install_date) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			statement = conn.prepareStatement(sql);

			try {
				statement.setInt(1, Integer.parseInt(post.get("case_id")));
				statement.setInt(2, Integer.parseInt(this.outletID + ""));
				statement.setString(3, outlet_dtl);
				statement.setString(4, post.get("esa_ref_no"));
				statement.setString(5, post.get("no_of_terminal"));
				statement.setString(6, post.get("terminal_rental_fee"));
				statement.setString(7, post.get("terminal_owner"));
				statement.setDouble(8, (post.get("terminal_deposit_wired").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_deposit_wired"))));
				statement.setDouble(9, (post.get("terminal_deposit_wireless").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_deposit_wireless"))));
				statement.setDouble(10, (post.get("terminal_rental_deposit_wired").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_rental_deposit_wired"))));
				statement.setDouble(11, (post.get("terminal_rental_deposit_wireless").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_rental_deposit_wireless"))));
				statement.setDouble(12, (post.get("advance_terminal_rental").length() <= 0 ? 0.00 : Double.parseDouble(post.get("advance_terminal_rental"))));
				statement.setDouble(13, (post.get("total_cheque_amt").length() <= 0 ? 0.00 : Double.parseDouble(post.get("total_cheque_amt"))));
				statement.setString(14, post.get("bank_issuer") + "|" + post.get("bank_branch"));
				statement.setString(15, post.get("cheque_no"));
				statement.setString(16, post.get("cheque_date"));
				statement.setString(17, post.get("terminal_id"));
				statement.setString(18, post.get("terminal_id_no"));
				statement.setString(19, post.get("terminal_install_date"));

				statement.execute();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public Outlet getOutlet(int outlet_id) {
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			String sql = "select * from outlet where o_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setInt(1, outlet_id);

				ResultSet resultSet = statement.executeQuery();

				try {
					if(resultSet.next()){
						Outlet o = new Outlet();

						o.setId(Long.parseLong(resultSet.getString("o_id")));
						o.setName(resultSet.getString("o_name"));
						o.setLicenseNo(resultSet.getString("o_license_no"));
						o.setLicenseType(resultSet.getString("o_license_type"));
						o.setLicenseIssuer(resultSet.getString("o_license_issuer"));
						o.setLicenseState(resultSet.getString("o_license_state"));
						o.setLicenseExpiry(resultSet.getString("o_license_expiry"));
						o.setRiskLevel(resultSet.getString("o_risk"));
						o.setCTName(resultSet.getString("o_ct_name"));
						o.setCTDesignation(resultSet.getString("o_ct_designation"));
						o.setCTEmail(resultSet.getString("o_ct_email"));
						o.setCTTel(resultSet.getString("o_ct_tel"));
						o.setCTFax(resultSet.getString("o_ct_fax"));
						o.setPaymentOpt(resultSet.getString("o_pymt_option"));
						o.setStatementOpt(resultSet.getString("o_stmt_option"));
						o.setOtherInfo(resultSet.getString("o_other_info"));
						o.setAddress(resultSet.getString("o_address"));
						o.setAddress2(resultSet.getString("o_address_2"));
						o.setAddress3(resultSet.getString("o_address_3"));

						Merchant m = new Merchant();
						m.setId(Long.parseLong(resultSet.getString("o_merchant_id")));
						m.setRegisteredName(resultSet.getString("o_reg_name"));
						m.setRegisteredNo(resultSet.getString("o_reg_no"));

						o.setMerchant(m);

						return o;
					}
				} finally {
					resultSet.close();
				}
			} finally {
				statement.close();
			}

			this.message = "Outlet not found.";

		} catch (Exception e){
			this.message = e.getMessage();
			e.printStackTrace();
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}

		return null;
	}

	public CaseOutlet getCaseOutlet(int case_id, int outlet_id) {
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			String sql = "select * from caseoutlet where co_case_id = ? and co_outlet_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setInt(1, case_id);
				statement.setInt(2, outlet_id);

				ResultSet resultSet = statement.executeQuery();

				try {
					if(resultSet.next()){
						CaseOutlet a = new CaseOutlet();

						a.setCaseId(Long.parseLong(resultSet.getString("co_case_id")));
						a.setOutletDtl(resultSet.getString("co_outlet_details"));
						a.setESARefNo(resultSet.getString("co_esa_ref_no"));
						a.setNumTerminal(resultSet.getString("co_num_terminal"));
						a.setTrmRental(resultSet.getString("co_trm_rental"));
						a.setTrmOwner(resultSet.getString("co_trm_owner"));
						a.setTrmDepositWired(resultSet.getString("co_trm_deposit_wired"));
						a.setTrmDepositWireless(resultSet.getString("co_trm_deposit_wireless"));
						a.setTrmRentalDepositWired(resultSet.getString("co_trm_rental_deposit_wired"));
						a.setTrmRentalDepositWireless(resultSet.getString("co_trm_rental_deposit_wireless"));
						a.setTrmRentalAdvance(resultSet.getString("co_trm_rental_advance"));
						a.setChequeAmt(resultSet.getString("co_cheque_amt"));
						a.setChequeIssuer(resultSet.getString("co_cheque_issuer"));
						a.setChequeNo(resultSet.getString("co_cheque_no"));
						a.setChequeDate(resultSet.getString("co_cheque_date"));
						a.setTerminalID(resultSet.getString("co_terminal_id"));
						a.setTerminalIDNo(resultSet.getString("co_terminal_id_no"));
						a.setTerminalInstallDate(resultSet.getString("co_terminal_install_date"));
						a.setRecommendation(resultSet.getString("co_recommendation"));
						a.setPosForm(resultSet.getString("co_pos_form"));
						a.setPosInstallation(resultSet.getString("co_installation"));
						a.setMidPOS(resultSet.getString("co_mid_pos"));
						a.setMidAMEX(resultSet.getString("co_mid_amex"));

						JSONObject obj = (JSONObject)JSONValue.parse(resultSet.getString("co_outlet_details"));

						Outlet o = new Outlet();
						o.setId(Long.parseLong(resultSet.getString("co_outlet_id")));
						o.setName(obj.get("trading_name").toString());
						o.setLicenseNo(obj.get("license_no").toString());
						o.setLicenseType(obj.get("license_type").toString());
						o.setLicenseIssuer(obj.get("issuer").toString());
						o.setLicenseState(obj.get("state").toString());
						o.setLicenseExpiry(obj.get("expiry_date").toString());
						o.setRiskLevel(obj.get("risk_level").toString());
						o.setCTName(obj.get("contact_person").toString());
						o.setCTDesignation(obj.get("designation").toString());
						o.setCTEmail(obj.get("email").toString());
						o.setCTTel(obj.get("telephone_no").toString());
						o.setCTFax(obj.get("fax_no").toString());
						o.setPaymentOpt(resultSet.getString("co_pymt_option"));
						o.setStatementOpt(resultSet.getString("co_stmt_option"));
						o.setOtherInfo(resultSet.getString("co_other_info"));
						o.setAddress(resultSet.getString("co_address"));
						o.setAddress2(resultSet.getString("co_address_2"));
						o.setAddress3(resultSet.getString("co_address_3"));
						o.setMDRRate(resultSet.getString("co_mdr_rate"));
						o.setEsapl(resultSet.getString("co_esapl"));
						o.setEzypay(resultSet.getString("co_ezy"));

						Merchant m = new Merchant();
						m.setId(Long.parseLong(obj.get("merchant_id").toString()));
						m.setRegisteredName(obj.get("registered_name").toString());
						m.setRegisteredNo(obj.get("registered_no").toString());

						o.setMerchant(m);

						a.setOutlet(o);

						return a;
					}
				} finally {
					resultSet.close();
				}

			} finally {
				statement.close();
			}

			this.message = "Case Outlet not found.";

		} catch (Exception e){
			this.message = e.getMessage();
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}

		return null;
	}

	public CaseOutlet getCaseOutletBySerialNo(String serial_no) {
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "select * from caseoutlet "
					+ "left join caseposform on co_case_id = pos_case_id and co_outlet_id = pos_outlet_id "
					+ "where pos_serial_no = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, serial_no);

				ResultSet resultSet = statement.executeQuery();

				try {
					if(resultSet.next()){
						CaseOutlet a = new CaseOutlet();

						a.setCaseId(Long.parseLong(resultSet.getString("co_case_id")));
						a.setOutletDtl(resultSet.getString("co_outlet_details"));
						a.setESARefNo(resultSet.getString("co_esa_ref_no"));
						a.setNumTerminal(resultSet.getString("co_num_terminal"));
						a.setTrmRental(resultSet.getString("co_trm_rental"));
						a.setTrmOwner(resultSet.getString("co_trm_owner"));
						a.setTrmDepositWired(resultSet.getString("co_trm_deposit_wired"));
						a.setTrmDepositWireless(resultSet.getString("co_trm_deposit_wireless"));
						a.setTrmRentalDepositWired(resultSet.getString("co_trm_rental_deposit_wired"));
						a.setTrmRentalDepositWireless(resultSet.getString("co_trm_rental_deposit_wireless"));
						a.setTrmRentalAdvance(resultSet.getString("co_trm_rental_advance"));
						a.setChequeAmt(resultSet.getString("co_cheque_amt"));
						a.setChequeIssuer(resultSet.getString("co_cheque_issuer"));
						a.setChequeNo(resultSet.getString("co_cheque_no"));
						a.setChequeDate(resultSet.getString("co_cheque_date"));
						a.setTerminalID(resultSet.getString("co_terminal_id"));
						a.setTerminalIDNo(resultSet.getString("co_terminal_id_no"));
						a.setTerminalInstallDate(resultSet.getString("co_terminal_install_date"));
						a.setRecommendation(resultSet.getString("co_recommendation"));
						a.setPosForm(resultSet.getString("co_pos_form"));
						a.setPosInstallation(resultSet.getString("co_installation"));
						a.setMidPOS(resultSet.getString("co_mid_pos"));
						a.setMidAMEX(resultSet.getString("co_mid_amex"));

						JSONObject obj = (JSONObject)JSONValue.parse(resultSet.getString("co_outlet_details"));

						Outlet o = new Outlet();
						o.setId(Long.parseLong(resultSet.getString("co_outlet_id")));
						o.setName(obj.get("trading_name").toString());
						o.setLicenseNo(obj.get("license_no").toString());
						o.setLicenseType(obj.get("license_type").toString());
						o.setLicenseIssuer(obj.get("issuer").toString());
						o.setLicenseState(obj.get("state").toString());
						o.setLicenseExpiry(obj.get("expiry_date").toString());
						o.setRiskLevel(obj.get("risk_level").toString());
						o.setCTName(obj.get("contact_person").toString());
						o.setCTDesignation(obj.get("designation").toString());
						o.setCTEmail(obj.get("email").toString());
						o.setCTTel(obj.get("telephone_no").toString());
						o.setCTFax(obj.get("fax_no").toString());
						o.setPaymentOpt(resultSet.getString("co_pymt_option"));
						o.setStatementOpt(resultSet.getString("co_stmt_option"));
						o.setOtherInfo(resultSet.getString("co_other_info"));
						o.setAddress(resultSet.getString("co_address"));
						o.setAddress2(resultSet.getString("co_address_2"));
						o.setAddress3(resultSet.getString("co_address_3"));
						o.setMDRRate(resultSet.getString("co_mdr_rate"));
						o.setEsapl(resultSet.getString("co_esapl"));
						o.setEzypay(resultSet.getString("co_ezy"));

						Merchant m = new Merchant();
						m.setId(Long.parseLong(obj.get("merchant_id").toString()));
						m.setRegisteredName(obj.get("registered_name").toString());
						m.setRegisteredNo(obj.get("registered_no").toString());

						o.setMerchant(m);

						a.setOutlet(o);

						return a;
					}
				} finally {
					resultSet.close();
				}

			} finally {
				statement.close();
			}

			this.message = "Case Outlet not found.";

		} catch (Exception e){
			this.message = e.getMessage();
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}

		return null;
	}

	public boolean editCaseOutlet(int case_id, int outlet_id, TreeMap<String, String> post, String user){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "update outlet set o_modified_date = sysdate, o_modified_by = ?, ";

			if(post.get("payment_info") != null) {
				sql += "o_pymt_option = ?, ";
			}

			if(post.get("statement_opt") != null) {
				sql += "o_stmt_option = ?, ";
			}

			if(post.get("other_info") != null) {
				sql += "o_other_info = ?, ";
			}

			if(post.get("physical_address") != null) {
				sql += "o_address = ?, ";
			}

			if(post.get("payment_address") != null) {
				sql += "o_address_2 = ?, ";
			}

			if(post.get("correspondence_address") != null) {
				sql += "o_address_3 = ?, ";
			}

			if(post.get("registered_name") != null) {
				sql += "o_reg_name = ?, ";
			}

			if(post.get("registered_no") != null) {
				sql += "o_reg_no = ?, ";
			}

			if(post.get("trading_name") != null) {
				sql += "o_name = ?, ";
			}

			if(post.get("contact_person") != null) {
				sql += "o_ct_name = ?, ";
			}

			if(post.get("designation") != null) {
				sql += "o_ct_designation = ?, ";
			}

			if(post.get("email") != null) {
				sql += "o_ct_email = ?, ";
			}

			if(post.get("telephone_no") != null) {
				sql += "o_ct_tel = ?, ";
			}

			if(post.get("fax_no") != null) {
				sql += "o_ct_fax = ?, ";
			}

			if(post.get("license_no") != null) {
				sql += "o_license_no = ?, ";
			}

			if(post.get("license_type") != null) {
				sql += "o_license_type = ?, ";
			}

			if(post.get("state") != null) {
				sql += "o_license_state = ?, ";
			}

			if(post.get("issuer") != null) {
				sql += "o_license_issuer = ?, ";
			}

			if(post.get("expiry_date") != null) {
				sql += "o_license_expiry = ?, ";
			}

			if(post.get("risk_level") != null) {
				sql += "o_risk = ?, ";
			}

			if(post.get("mdr") != null) {
				sql += "o_mdr_rate = ?, ";
			}

			if(post.get("esapl") != null) {
				sql += "o_esapl = ?, ";
			}

			if(post.get("ezypay") != null) {
				sql += "o_ezy = ?, ";
			}
			sql = sql.substring(0, sql.lastIndexOf(','));

			sql += " where o_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				int paramIndex = 1;
				statement.setString(paramIndex++, user);

				if(post.get("payment_info") != null) {
					statement.setString(paramIndex++, post.get("payment_info"));
				}

				if(post.get("statement_opt") != null) {
					statement.setString(paramIndex++, post.get("statement_opt"));
				}

				if(post.get("other_info") != null) {
					statement.setString(paramIndex++, post.get("other_info"));
				}

				if(post.get("physical_address") != null) {
					statement.setString(paramIndex++, post.get("physical_address"));
				}

				if(post.get("payment_address") != null) {
					statement.setString(paramIndex++, post.get("payment_address"));
				}

				if(post.get("correspondence_address") != null) {
					statement.setString(paramIndex++, post.get("correspondence_address"));
				}

				if(post.get("registered_name") != null) {
					statement.setString(paramIndex++, post.get("registered_name"));
				}

				if(post.get("registered_no") != null) {
					statement.setString(paramIndex++, post.get("registered_no"));
				}

				if(post.get("trading_name") != null) {
					statement.setString(paramIndex++, post.get("trading_name"));
				}

				if(post.get("contact_person") != null) {
					statement.setString(paramIndex++, post.get("contact_person"));
				}

				if(post.get("designation") != null) {
					statement.setString(paramIndex++, post.get("designation"));
				}

				if(post.get("email") != null) {
					statement.setString(paramIndex++, post.get("email"));
				}

				if(post.get("telephone_no") != null) {
					statement.setString(paramIndex++, post.get("telephone_no"));
				}

				if(post.get("fax_no") != null) {
					statement.setString(paramIndex++, post.get("fax_no"));
				}

				if(post.get("license_no") != null) {
					statement.setString(paramIndex++, post.get("license_no"));
				}

				if(post.get("license_type") != null) {
					statement.setString(paramIndex++, post.get("license_type"));
				}

				if(post.get("state") != null) {
					statement.setString(paramIndex++, post.get("state"));
				}

				if(post.get("issuer") != null) {
					statement.setString(paramIndex++, post.get("issuer"));
				}

				if(post.get("expiry_date") != null) {
					statement.setString(paramIndex++, post.get("expiry_date"));
				}

				if(post.get("risk_level") != null) {
					statement.setString(paramIndex++, post.get("risk_level"));
				}

				if(post.get("mdr") != null) {
					statement.setString(paramIndex++, post.get("mdr"));
				}

				if(post.get("esapl") != null) {
					statement.setString(paramIndex++, post.get("esapl"));
				}

				if(post.get("ezypay") != null) {
					statement.setString(paramIndex++, post.get("ezypay"));
				}

				statement.setInt(paramIndex, outlet_id);

				statement.execute();
			} finally {
				statement.close();
			}


			Outlet outlet = getOutlet(outlet_id);
			if(outlet == null){
				System.out.println("here");
				return false;
			}

			JSONObject outlet_dtl_obj = new JSONObject();
			outlet_dtl_obj.put("merchant_id", outlet.getMerchant().getId());
			outlet_dtl_obj.put("trading_name", outlet.getName());
			outlet_dtl_obj.put("registered_name", outlet.getMerchant().getRegisteredName());
			outlet_dtl_obj.put("registered_no", outlet.getMerchant().getRegisteredNo());
			outlet_dtl_obj.put("license_no", outlet.getLisenceNo());
			outlet_dtl_obj.put("license_type", outlet.getLicenseType());
			outlet_dtl_obj.put("issuer", outlet.getLicenseIssuer());
			outlet_dtl_obj.put("state", outlet.getLicenseState());
			outlet_dtl_obj.put("expiry_date", outlet.getLicenseExpiry());
			outlet_dtl_obj.put("risk_level", outlet.getRiskLevel());
			outlet_dtl_obj.put("contact_person", outlet.getCTName());
			outlet_dtl_obj.put("designation", outlet.getCTDesignation());
			outlet_dtl_obj.put("email", outlet.getCTEmail());
			outlet_dtl_obj.put("telephone_no", outlet.getCTTel());
			outlet_dtl_obj.put("fax_no", outlet.getCTFax());
			outlet_dtl_obj.put("address", outlet.getAddress());
			outlet_dtl_obj.put("address_2", outlet.getAddress2());
			outlet_dtl_obj.put("address_3", outlet.getAddress3());
			outlet_dtl_obj.put("mdr_rate", outlet.getMDRRate());
			outlet_dtl_obj.put("esapl", outlet.getEsapl());
			outlet_dtl_obj.put("ezypay", outlet.getEzypay());
			outlet_dtl_obj.put("stmt_option", outlet.getStatementOpt());
			outlet_dtl_obj.put("pymt_option", outlet.getPaymentOpt());
			outlet_dtl_obj.put("other_info", outlet.getOtherInfo());
			outlet_dtl_obj.put("gst_rate", outlet.getGst_rate());
			outlet_dtl_obj.put("gst_type", outlet.getGst_type());
			String outlet_dtl = outlet_dtl_obj.toString();


			sql = "update caseoutlet set co_outlet_details = ?, ";

			if(post.get("payment_info") != null) {
				sql += "co_pymt_option = ?, ";
			}

			if(post.get("statement_opt") != null) {
				sql += "co_stmt_option = ?, ";
			}

			if(post.get("other_info") != null) {
				sql += "co_other_info = ?, ";
			}

			if(post.get("physical_address") != null) {
				sql += "co_address = ?, ";
			}

			if(post.get("payment_address") != null) {
				sql += "co_address_2 = ?, ";
			}

			if(post.get("correspondence_address") != null) {
				sql += "co_address_3 = ?, ";
			}

			if(post.get("esa_ref_no") != null) {
				sql += "co_esa_ref_no = ?, ";
			}

			if(post.get("no_of_terminal") != null) {
				sql += "co_num_terminal = ?, ";
			}

			if(post.get("terminal_rental_fee") != null) {
				sql += "co_trm_rental = ?, ";
			}

			if(post.get("terminal_owner") != null) {
				sql += "co_trm_owner = ?, ";
			}

			if(post.get("terminal_deposit_wired") != null) {
				sql += "co_trm_deposit_wired = ?, ";
			}

			if(post.get("terminal_deposit_wireless") != null) {
				sql += "co_trm_deposit_wireless = ?, ";
			}

			if(post.get("terminal_rental_deposit_wired") != null) {
				sql += "co_trm_rental_deposit_wired = ?, ";
			}

			if(post.get("advance_terminal_rental") != null) {
				sql += "co_trm_rental_advance = ?, ";
			}

			if(post.get("total_cheque_amt") != null) {
				sql += "co_cheque_amt = ?, ";
			}

			if(post.get("bank_issuer") != null || post.get("bank_branch") != null) {
				sql += "co_cheque_issuer = ?, ";
			}

			if(post.get("cheque_no") != null) {
				sql += "co_cheque_no = ?, ";
			}

			if(post.get("cheque_date") != null) {
				sql += "co_cheque_date = ?, ";
			}

			if(post.get("terminal_id") != null) {
				sql += "co_terminal_id = ?, ";
			}

			if(post.get("terminal_id_no") != null) {
				sql += "co_terminal_id_no = ?, ";
			}

			if(post.get("terminal_install_date") != null) {
				sql += "co_terminal_install_date = ?, ";
			}

			if(post.get("mdr") != null) {
				sql += "co_mdr_rate = ?, ";
			}

			if(post.get("esapl") != null) {
				sql += "co_esapl = ?, ";
			}

			if(post.get("ezypay") != null) {
				sql += "co_ezy = ?, ";
			}

			if(post.get("mid_pos") != null) {
				sql += "co_mid_pos = ?, ";
			}

			if(post.get("mid_amex") != null) {
				sql += "co_mid_amex = ?, ";
			}

			sql = sql.substring(0, sql.lastIndexOf(','));

			sql += " where co_case_id = ? and co_outlet_id = ?";

			System.out.println(sql);
			System.out.println("CaseID : " + case_id);
			System.out.println("Outlet ID : " + outlet_id);
			System.out.println("Payment Info : " + post.get("payment_info"));

			statement = conn.prepareStatement(sql);

			try {
				int paramIndex = 1;
				statement.setString(paramIndex++, outlet_dtl);

				if(post.get("payment_info") != null) {
					statement.setString(paramIndex++, post.get("payment_info"));
				}

				if(post.get("statement_opt") != null) {
					statement.setString(paramIndex++, post.get("statement_opt"));
				}

				if(post.get("other_info") != null) {
					statement.setString(paramIndex++, post.get("other_info"));
				}

				if(post.get("physical_address") != null) {
					statement.setString(paramIndex++, post.get("physical_address"));
				}

				if(post.get("payment_address") != null) {
					statement.setString(paramIndex++, post.get("payment_address"));
				}

				if(post.get("correspondence_address") != null) {
					statement.setString(paramIndex++, post.get("correspondence_address"));
				}

				if(post.get("esa_ref_no") != null) {
					statement.setString(paramIndex++, post.get("esa_ref_no"));
				}

				if(post.get("no_of_terminal") != null) {
					statement.setString(paramIndex++, post.get("no_of_terminal"));
				}

				if(post.get("terminal_rental_fee") != null) {
					statement.setString(paramIndex++, post.get("terminal_rental_fee"));
				}

				if(post.get("terminal_owner") != null) {
					statement.setString(paramIndex++, post.get("terminal_owner"));
				}

				if(post.get("terminal_deposit_wired") != null) {
					statement.setDouble(paramIndex++, (post.get("terminal_deposit_wired").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_deposit_wired"))));
				}

				if(post.get("terminal_deposit_wireless") != null) {
					statement.setDouble(paramIndex++, (post.get("terminal_deposit_wireless").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_deposit_wireless"))));
				}

				if(post.get("terminal_rental_deposit_wired") != null) {
					statement.setDouble(paramIndex++, (post.get("terminal_rental_deposit_wired").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_rental_deposit_wired"))));
				}

				if(post.get("advance_terminal_rental") != null) {
					statement.setDouble(paramIndex++, (post.get("advance_terminal_rental").length() <= 0 ? 0.00 : Double.parseDouble(post.get("advance_terminal_rental"))));
				}

				if(post.get("total_cheque_amt") != null) {
					statement.setDouble(paramIndex++, (post.get("total_cheque_amt").length() <= 0 ? 0.00 : Double.parseDouble(post.get("total_cheque_amt"))));
				}

				if(post.get("bank_issuer") != null || post.get("bank_branch") != null) {
					statement.setString(paramIndex++, (post.get("bank_issuer") != null ? post.get("bank_issuer") : "") + "|" + (post.get("bank_branch") != null ? post.get("bank_branch") : ""));
				}

				if(post.get("cheque_no") != null) {
					statement.setString(paramIndex++, post.get("cheque_no"));
				}

				if(post.get("cheque_date") != null) {
					statement.setString(paramIndex++, post.get("cheque_date"));
				}

				if(post.get("terminal_id") != null) {
					statement.setString(paramIndex++, post.get("terminal_id"));
				}

				if(post.get("terminal_id_no") != null) {
					statement.setString(paramIndex++, post.get("terminal_id_no"));
				}

				if(post.get("terminal_install_date") != null) {
					statement.setString(paramIndex++, post.get("terminal_install_date"));
				}

				if(post.get("mdr") != null) {
					statement.setString(paramIndex++, post.get("mdr"));
				}

				if(post.get("esapl") != null) {
					statement.setString(paramIndex++, post.get("esapl"));
				}

				if(post.get("ezypay") != null) {
					statement.setString(paramIndex++, post.get("ezypay"));
				}

				if(post.get("mid_pos") != null) {
					statement.setString(paramIndex++, post.get("mid_pos"));
				}

				if(post.get("mid_amex") != null) {
					statement.setString(paramIndex++, post.get("mid_amex"));
				}


				statement.setInt(paramIndex++, case_id);
				statement.setInt(paramIndex, outlet_id);

				statement.execute();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			e.printStackTrace();
			this.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}


	public boolean deleteCaseOutlet(int case_id, int outlet_id, String user){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "delete from caseoutlet where co_case_id = ? and co_outlet_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setInt(1, case_id);
				statement.setInt(2, outlet_id);

				statement.execute();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public ArrayList<CaseOutlet> getCaseOutlets(int case_id) {
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		ArrayList<CaseOutlet> coArr = new ArrayList<CaseOutlet>();

		try {
			String sql = "select * from caseoutlet where co_case_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setInt(1, case_id);

				ResultSet resultSet = statement.executeQuery();

				try {
					while(resultSet.next()){
						CaseOutlet a = new CaseOutlet();

						a.setCaseId(Long.parseLong(resultSet.getString("co_case_id")));
						a.setOutletDtl(resultSet.getString("co_outlet_details"));
						a.setESARefNo(resultSet.getString("co_esa_ref_no"));
						a.setNumTerminal(resultSet.getString("co_num_terminal"));
						a.setTrmRental(resultSet.getString("co_trm_rental"));
						a.setTrmOwner(resultSet.getString("co_trm_owner"));
						a.setTrmDepositWired(resultSet.getString("co_trm_deposit_wired"));
						a.setTrmDepositWireless(resultSet.getString("co_trm_deposit_wireless"));
						a.setTrmRentalDepositWired(resultSet.getString("co_trm_rental_deposit_wired"));
						a.setTrmRentalDepositWireless(resultSet.getString("co_trm_rental_deposit_wireless"));
						a.setTrmRentalAdvance(resultSet.getString("co_trm_rental_advance"));
						a.setChequeAmt(resultSet.getString("co_cheque_amt"));
						a.setChequeIssuer(resultSet.getString("co_cheque_issuer"));
						a.setChequeNo(resultSet.getString("co_cheque_no"));
						a.setChequeDate(resultSet.getString("co_cheque_date"));
						a.setRecommendation(resultSet.getString("co_recommendation"));

						JSONObject obj = (JSONObject)JSONValue.parse(resultSet.getString("co_outlet_details"));

						Outlet o = new Outlet();
						o.setId(Long.parseLong(resultSet.getString("co_outlet_id")));
						o.setName(obj.get("trading_name").toString());
						o.setLicenseNo(obj.get("license_no").toString());
						o.setLicenseType(obj.get("license_type").toString());
						o.setLicenseIssuer(obj.get("issuer").toString());
						o.setLicenseState(obj.get("state").toString());
						o.setLicenseExpiry(obj.get("expiry_date").toString());
						o.setRiskLevel(obj.get("risk_level").toString());
						o.setCTName(obj.get("contact_person").toString());
						o.setCTDesignation(obj.get("designation").toString());
						o.setCTEmail(obj.get("email").toString());
						o.setCTTel(obj.get("telephone_no").toString());
						o.setCTFax(obj.get("fax_no").toString());
						o.setPaymentOpt(resultSet.getString("co_pymt_option"));
						o.setStatementOpt(resultSet.getString("co_stmt_option"));
						o.setOtherInfo(resultSet.getString("co_other_info"));
						o.setAddress(resultSet.getString("co_address"));
						o.setAddress2(resultSet.getString("co_address_2"));
						o.setAddress3(resultSet.getString("co_address_3"));
						o.setMDRRate(resultSet.getString("co_mdr_rate"));
						o.setEsapl(resultSet.getString("co_esapl"));
						o.setEzypay(resultSet.getString("co_ezy"));

						Merchant m = new Merchant();
						m.setId(Long.parseLong(obj.get("merchant_id").toString()));
						m.setRegisteredName(obj.get("registered_name").toString());
						m.setRegisteredNo(obj.get("registered_no").toString());

						o.setMerchant(m);

						a.setOutlet(o);

						coArr.add(a);
					}
				} finally {
					resultSet.close();
				}

			} finally {
				statement.close();
			}

			return coArr;

		} catch (Exception e){
			this.message = e.getMessage();
			return null;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public boolean editCaseOutletRecom(int case_id, int outlet_id, String recom){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			String sql = "update caseoutlet set co_recommendation = ? where co_case_id = ? and co_outlet_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, recom);
				statement.setInt(2, case_id);
				statement.setInt(3, outlet_id);

	            statement.execute();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			this.message = e.getMessage();
			e.printStackTrace();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public boolean editCaseOutletPOSForm(int case_id, int outlet_id, String pos_form){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "update caseoutlet set co_pos_form = ? "
					+ "where co_case_id = ? "
					+ "	and co_outlet_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, pos_form);
				statement.setInt(2, case_id);
				statement.setInt(3, outlet_id);

	            statement.execute();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public boolean editCaseOutletPOSForm(int case_id, int outlet_id, String pos_form, String terminalNum, String terminalID){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "update caseoutlet set co_pos_form = ?, "
					+ "	co_terminal_id = ?, "
					+ " co_terminal_id_no = ? "
					+ "where co_case_id = ? "
					+ "	and co_outlet_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, pos_form);
				statement.setString(2, terminalID);
				statement.setString(3, terminalNum);
				statement.setInt(4, case_id);
				statement.setInt(5, outlet_id);

	            statement.execute();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}

	public boolean editPOSFormSerialNo(int case_id, int outlet_id, String vendor_name, String serial_no){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {
			boolean hasResult = false;

			String sql = "select * from  caseposform "
					+ "where pos_case_id = ? "
					+ "and pos_outlet_id = ? "
					+ "and pos_vendor_name = ? ";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setInt(1, case_id);
				statement.setInt(2, outlet_id);
				statement.setString(3, vendor_name);

				ResultSet resultSet = statement.executeQuery();

				try {
					hasResult = resultSet.next();
				} finally {
					resultSet.close();
				}

			} finally {
				statement.close();
			}


			if(hasResult == false) {
				sql = "insert into caseposform(pos_case_id, pos_outlet_id, pos_vendor_name, pos_serial_no) "
						+ "values(?, ?, ?, ?)";

				statement = conn.prepareStatement(sql);

				try {
					statement.setInt(1, case_id);
					statement.setInt(2, outlet_id);
					statement.setString(3, vendor_name);
					statement.setString(4, serial_no);

		            statement.execute();
				} finally {
					statement.close();
				}
			}

			return true;

		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}


	public boolean editCaseOutletInstallForm(int case_id, int outlet_id, String pos_install, String terminal_install_date){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "update caseoutlet set co_installation = ?, "
					+ "co_terminal_install_date = ? "
					+ "where co_case_id = ? "
					+ "	and co_outlet_id = ?";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setString(1, pos_install);
				statement.setString(2, terminal_install_date);
				statement.setInt(3, case_id);
				statement.setInt(4, outlet_id);

	            statement.execute();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}


	public boolean install_note_submit(int case_id, int outlet_id, String vendor_name, String remark, String status, String reschd_date, String user){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
		try {

			String sql = "insert into caseinstallnote(in_case_id, in_outlet_id, in_vendor_name, in_remark, in_status, in_reschedule_date, in_created_by, in_created_date) "
				+ "values(?, ?, ?, ?, ?, ?, ?, sysdate)";

			PreparedStatement statement = conn.prepareStatement(sql);

			try {
				statement.setLong(1, case_id);
				statement.setLong(2, outlet_id);
				statement.setString(3,  vendor_name);
				statement.setString(4, remark.length() > 0 ? remark : null);
				statement.setString(5, status);
				statement.setString(6, reschd_date.length() > 0 ? reschd_date : null);
				statement.setString(7, user);

	            statement.execute();
			} finally {
				statement.close();
			}

			return true;

		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}
}
